/*
 *  Лицензия MIT
 * 
 *  Copyright 2011 Salnikov Aleksey.
 * 
 *  Данная лицензия разрешает, безвозмездно, лицам, получившим копию данного программного обеспечения
 *  и сопутствующей документации (в дальнейшем именуемыми "Программное Обеспечение"),
 *  использовать Программное Обеспечение без ограничений, включая неограниченное право
 *  на использование, копирование, изменение, объединение, публикацию, распространение,
 *  сублицензирование и/или продажу копий Программного Обеспечения, также как и лицам,
 *  которым предоставляется данное Программное Обеспечение, при соблюдении следующих условий:
 * 
 *  Вышеупомянутый копирайт и данные условия должны быть включены во все копии или значимые
 *  части данного Программного Обеспечения.
 * 
 *  ДАННОЕ ПРОГРАММНОЕ ОБЕСПЕЧЕНИЕ ПРЕДОСТАВЛЯЕТСЯ «КАК ЕСТЬ», БЕЗ ЛЮБОГО ВИДА ГАРАНТИЙ,
 *  ЯВНО ВЫРАЖЕННЫХ ИЛИ ПОДРАЗУМЕВАЕМЫХ, ВКЛЮЧАЯ, НО НЕ ОГРАНИЧИВАЯСЬ ГАРАНТИЯМИ ТОВАРНОЙ ПРИГОДНОСТИ,
 *  СООТВЕТСТВИЯ ПО ЕГО КОНКРЕТНОМУ НАЗНАЧЕНИЮ И НЕНАРУШЕНИЯ ПРАВ. НИ В КАКОМ СЛУЧАЕ АВТОРЫ ИЛИ ПРАВООБЛАДАТЕЛИ
 *  НЕ НЕСУТ ОТВЕТСТВЕННОСТИ ПО ИСКАМ О ВОЗМЕЩЕНИИ УЩЕРБА, УБЫТКОВ ИЛИ ДРУГИХ ТРЕБОВАНИЙ
 *  ПО ДЕЙСТВУЮЩИМ КОНТРАКТАМ, ДЕЛИКТАМ ИЛИ ИНОМУ, ВОЗНИКШИМ ИЗ, ИМЕЮЩИМ ПРИЧИНОЙ ИЛИ СВЯЗАННЫМ
 *  С ПРОГРАММНЫМ ОБЕСПЕЧЕНИЕМ ИЛИ ИСПОЛЬЗОВАНИЕМ ПРОГРАММНОГО ОБЕСПЕЧЕНИЯ
 *  ИЛИ ИНЫМИ ДЕЙСТВИЯМИ С ПРОГРАММНЫМ ОБЕСПЕЧЕНИЕМ.
 */

package database;

/**
 *  Класс, обеспечивающий работу с базой данных
 *
 * @author Salnikov Aleksey
 */

import CommonTools.Admin;
import CommonTools.Constants;
import CommonTools.Demand;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Vector;

public class Db
{

    protected String driverString = "org.apache.derby.jdbc.EmbeddedDriver";
    //protected String db = "jdbc:derby:ordobase; create=true"; //Заменяем на время создания - это тестовая база
    protected String db = "jdbc:derby:ordobase; create=true"; //Это чистая база - она для выпуска продукции
    protected String port;
    protected Connection conn;
    protected Statement st;
    protected String serverName = "OrdoServer";

    public Db()
    {
        try
        {
            //Устанавливаем соединение с БД
            Class.forName(driverString);
            conn = DriverManager.getConnection(db);
            st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        }
        catch (Exception e){}

        //Регистрируем сервер
        
    }

    /**Метод для создания колонки в таблице заявок
    */
    //public static void main(String args[])
    //{
        //Создаем чистую БД для выкладки ее в проект
        /*Db db = new Db();
        String sql = "CREATE TABLE demand ("
                + "d_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "d_theme VARCHAR(120) NOT NULL,"
                + "d_subject VARCHAR(800) NOT NULL,"
                + "d_crit VARCHAR(20) NOT NULL,"
                + "d_status VARCHAR(50) NOT NULL,"
                + "d_time BIGINT,"
                + "t_time_exec BIGINT,"
                + "d_pc VARCHAR(50) NOT NULL,"
                + "d_comment VARCHAR(800),"
                + "d_t_id INTEGER,"
                + "d_t_id_execure INTEGER,"
                + "d_user VARCHAR(100) NOT NULL,"
                + "d_exec INTEGER DEFAULT 0,"
                + "PRIMARY KEY (d_id))";
        Db db = new Db();
        String dropSql = "CREATE TABLE system ("
                + "s_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "s_name VARCHAR(30) NOT NULL,"
                + "s_value VARCHAR (30),"
                + "PRIMARY KEY (s_id))";
        db.execute(dropSql);

        String add = "INSERT INTO system (s_name, s_value) VALUES ('autodelete', '1')";
        db.execute(add);*/
        //db.execute(sql);
        
        //db.addDemandFromClient("Тест", "Ну тестируем", "Высокая", "test-pc", "test-user");
    //}
     

    /**
     * Выполнение запроса с возвращением результата
     * @param sql Строка запроса
     * @return Объект ResultSet
     */
    protected ResultSet query(String sql)
    {
        try {
            return st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    /**
     * Выполнение запроса без возвращения результата
     * @param sql Строка запроса
     */
    public void execute(String sql)
    {
        try {
            st.execute(sql);
        } catch (SQLException ex) {
            Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Demand> getDemands(int a_id, boolean lastWeek) throws SQLException
    {
            String sql = "SELECT * FROM demand WHERE d_t_id=" + a_id;
            if (lastWeek) {
                //Получаем данные за последнюю неделю
            }
            ResultSet rs = query(sql);
            ArrayList<Demand> demands = new ArrayList<Demand>();
            if (countRecord(rs) > 0) {
                //Записываем данные в таблицу
                while (rs.next()) {
                    demands.add(new Demand(rs.getInt("d_id"),
                                           rs.getString("d_theme"),
                                           rs.getString("d_subject"),
                                           rs.getString("d_crit"),
                                           rs.getString("d_status"),
                                           rs.getLong("d_time"),
                                           rs.getLong("t_time_exec"),
                                           rs.getString("d_pc"),
                                           rs.getString("d_comment"),
                                           rs.getInt("d_t_id"),
                                           rs.getInt("d_t_id_execute"),
                                           rs.getString("d_user"),
                                           rs.getBoolean("d_exec")));
                }
           }
            return demands;
    }
    /**
     * Функция возвращает список всех заявок
     *
     * @param lastWeek если истина, то будет возвращены заявки за последнюю неделю
     * @return
     * @throws SQLException
     */
    public ArrayList<Demand> getAllDemand(boolean lastWeek) throws SQLException
    {
        String sql = "SELECT * FROM demand";
        if (lastWeek)
        {
            //Получаем данные за последнюю неделю
        }

        ResultSet rs = query(sql);
        ArrayList<Demand> demands = new ArrayList<Demand>();
        if (countRecord(rs) > 0)
        {
            //Записываем данные в таблицу
            while (rs.next())
            {
                demands.add(new Demand(
                        rs.getInt("d_id"),
                        rs.getString("d_theme"),
                        rs.getString("d_subject"),
                        rs.getString("d_crit"),
                        rs.getString("d_status"),
                        rs.getLong("d_time"),
                        rs.getLong("t_time_exec"),
                        rs.getString("d_pc"),
                        rs.getString("d_comment"),
                        rs.getInt("d_t_id"),
                        rs.getInt("d_t_id_execute"),
                        rs.getString("d_user"),
                        rs.getBoolean("d_exec")
                        ));
            }

        }

        return demands;
    }

    public ArrayList<Demand> getDemandsForClient(String pc, String user) throws SQLException
    {
        ArrayList<Demand> demands = new ArrayList<Demand>();
        String sql = "SELECT * FROM demand WHERE d_pc=\'" + pc + "\' AND d_user=\'" + user + "\'";

        ResultSet rs = query(sql);
        if (countRecord(rs) > 0)
        {
            //Записываем данные в таблицу
            while (rs.next())
            {
                demands.add(new Demand(
                        rs.getInt("d_id"),
                        rs.getString("d_theme"),
                        rs.getString("d_subject"),
                        rs.getString("d_crit"),
                        rs.getString("d_status"),
                        rs.getLong("d_time"),
                        rs.getLong("t_time_exec"),
                        rs.getString("d_pc"),
                        rs.getString("d_comment"),
                        rs.getInt("d_t_id"),
                        rs.getInt("d_t_id_execute"),
                        rs.getString("d_user"),
                        rs.getBoolean("d_exec")
                        ));
            }

        }

        return demands;
    }

    public ArrayList<Demand> getDemandsByFilter(int status, int time, int admin, String pc) throws SQLException
    {
        ArrayList<Demand> demands = new ArrayList<Demand>();

        String sql = "SELECT * FROM ";

        String statusSql, statusString;
        String timeSql, timeString;
        //String statusString;

        //Все заявки
        if (status == Constants.ALL_DEMANDS)
            //statusString = "d_exec=1 OR d_exec=0";
            statusString = "1=1";
        else
            if (status == Constants.EXECUTE_DEMANDS)
                statusString = "d_exec=1";
            else
                statusString = "d_exec!=1";

        //Формирование запроса по статусу заявки
        statusSql = "SELECT * FROM demand WHERE " + statusString;

        //Формирование строки с выборкой по времени заявки
        timeSql = "SELECT * FROM (" + statusSql + ") AS status_table";
        if (time == Constants.LAST_WEEK_DEMANDS)
        {
            Date d = new Date();
            long betweenTop = d.getTime();
            long betweenLow = betweenTop - Constants.WEEK_SECONDS;

            timeSql += " WHERE d_time BETWEEN " + betweenLow + " AND " + betweenTop;
        }
        else
        {
            //Заявки за последний месяц
            Date d = new Date();
            long betweenTop = d.getTime();
            long betweenLow = betweenTop - 2419200 - 1000000000 - 1000000000 - 416780800;
            timeSql += " WHERE d_time BETWEEN " + betweenLow + " AND " + betweenTop;
        }

        //формирование запроса по администратору
        sql = "SELECT * FROM (" + timeSql + ") AS time_table";
        if (admin != Constants.ALL_ADMINS)
            sql += " WHERE d_t_id_execute=" + admin;

        //Формируем запрос по компьютеру
        String sqlPc = "SELECT * FROM (" + sql + ") AS admins_table";
        if (!pc.equals("Все компьютеры"))
        {
            sqlPc += " WHERE d_pc=\'" + pc + "\'";
        }

        //Запрос и формирование массива для ответа
        ResultSet rs = query(sqlPc);
        //System.out.println("\n" + sql);
        //System.out.println(countRecord(rs));

        if (countRecord(rs) > 0)
        {
            //Записываем данные в таблицу
            while (rs.next())
            {
                demands.add(new Demand(
                        rs.getInt("d_id"),
                        rs.getString("d_theme"),
                        rs.getString("d_subject"),
                        rs.getString("d_crit"),
                        rs.getString("d_status"),
                        rs.getLong("d_time"),
                        rs.getLong("t_time_exec"),
                        rs.getString("d_pc"),
                        rs.getString("d_comment"),
                        rs.getInt("d_t_id"),
                        rs.getInt("d_t_id_execute"),
                        rs.getString("d_user"),
                        rs.getBoolean("d_exec")
                        ));

                //System.out.println(rs.getBoolean("d_exec"));
            }
        }

        return demands;
    }

    /**
     * Функция возвращает список администраторов
     *
     * @return Список администраторов
     * @throws SQLException
     */
    public ArrayList<Admin> getAdmins() throws SQLException
    {
        //Строка запроса. Выбираем всех администраторов
        String sql = "SELECT * FROM admins";

        ResultSet rs = query(sql);

        ArrayList<Admin> admins = new ArrayList<Admin>();
        if (countRecord(rs) > 0)
        {
            while (rs.next())
            {
                //Заполняем массив администраторами
                admins.add(new Admin(
                        rs.getInt("a_id"),
                        rs.getString("a_name"),
                        rs.getString("a_fam"),
                        rs.getString("a_otch"),
                        rs.getString("a_login"),
                        rs.getString("a_password"),
                        rs.getInt("a_root"),
                        rs.getString("a_mail"),
                        rs.getString("a_phone")
                        ));
            }
        }

        return admins;

    }

    /**
     * Фукнция добавления администратора
     *
     * @param name Имя
     * @param fam Фамилия
     * @param otch Отчество
     * @param login Логин
     * @param password Пароль
     * @param mail Адрес электронной почты
     * @param phone Телефон
     * @param photo Путь к фотографии
     */
    public void addAdmin(String name, String fam, String otch, String login, String password, int root, String mail, String phone)
    {
        String fields = "(a_name, a_fam, a_otch, a_login, a_password, a_root";
        if (mail.length() > 0) fields += ", a_mail";
        if (phone.length() > 0) fields += ", a_phone";
        fields += ") ";

        String values = " (\'" + name + "\', \'"
                        + fam + "\', \'" + otch
                        + "\', \'" + login + "\', \'"
                        + password + "\',"
                        + root;
        if (mail.length() > 0) values += ", \'" + mail + "\'";
        if (phone.length() > 0) values += ", \'" + phone + "\'";
        values += ")";

        String sql = "INSERT INTO admins " + fields + "VALUES" + values;

        //System.out.println(sql);
        this.execute(sql);

    }

    /**
     * Функция редактирования данных администратора
     *
     * @param id порядковый номер админа (по этому номеру будет идти редактирование в БД)
     * @param name имя
     * @param fam фамилия
     * @param otch отчество
     * @param login логин
     * @param password пароль
     * @param mail адрес электронной почты
     * @param phone телефон
     * @param photo адрес до фотографии
     */
    public void editAdmin(int id,String name, String fam, String otch, String login, String password, int root, String mail, String phone)
    {
        /*String sql = "UPDATE admins SET `a_name`=\"" + name + "\", `a_fam`=\"" + fam
                + "\", `a_otch`=\"" + otch + "\", `a_login`=\"" + login + "\", `a_password`=\"" + password
                + "\", `a_mail`=\"" + mail + "\", `a_phone`=" + phone + ", `a_photo`=\"" + photo + "\" WHERE `a_id`=" + id;*/

        String sql = "UPDATE admins SET a_name=\'" + name + "\', a_fam=\'" + fam
                + "\', a_otch=\'" + otch + "\', a_login=\'" + login + "\', a_password=\'" + password
                + "\', a_root=" + root + ", a_phone=\'" + phone + "\', a_mail=\'" + mail + "\'  WHERE a_id=" + id;

        this.execute(sql);

        //Если добавлен телефон
        /*
        if (phone.length() > 0)
        {
            sql = "UPDATE admins SET a_phone=\'" + phone + "\' WHERE a_id=" + id;
            this.execute(sql);
        }
        if (mail.length() > 0)
        {
            sql = "UPDATE admins SET a_mail=\'" + mail + "\' WHERE a_id=" + id;
            this.execute(sql);
        }*/
    }

    /**
     * Функция удаления администратора
     * @param id номер администратора из БД
     */
    public void deleteAdmin(int id)
    {
        String sql = "DELETE FROM admins WHERE a_id=" + id;
        this.execute(sql);
    }

    public Admin getAdminByLoginAndPassword(String login, String password) throws SQLException
    {
        String sql = "SELECT * FROM admins WHERE a_login=\'" + login + "\' AND a_password=\'" + password + "\'";
        ResultSet rs = this.query(sql);
        if (countRecord(rs) > 0)
        {
            rs.next();
            Admin admin = new Admin(
                        rs.getInt("a_id"),
                        rs.getString("a_name"),
                        rs.getString("a_fam"),
                        rs.getString("a_otch"),
                        rs.getString("a_login"),
                        rs.getString("a_password"),
                        rs.getInt("a_root"),
                        rs.getString("a_mail"),
                        rs.getString("a_phone")
                    );
            return admin;
        }
        return null;
    }

    /**
     * Функция добавления заявки от клиента
     *
     * @param theme заголовок заяки
     * @param subject описание проблемы
     * @param pc компьютер
     */
    public void addDemandFromClient(String theme, String subject, String crit, String pc, String user)
    {
        String sql = "INSERT INTO demand (d_theme, d_subject, d_crit, d_pc, d_status, d_exec, d_user, d_time) VALUES ";
        String values = "(\'" + theme + "\',"
                      + "\'" + subject + "\',"
                      + "\'" + crit + "\',"
                      + "\'" + pc + "\',"
                      + "\'Не рассмотрена\',"
                      + "0,"
                      + "\'" + user + "\',";
        Date d = new Date();
        values += d.getTime() + ")";
        sql += values;
        this.execute(sql);
    }

    /**
     * Функция удаления заявки
     * @param id номер заявки
     */
    public void deleteDemand(int id)
    {
        String sql = "DELETE FROM demand WHERE d_id=" + id;
        this.execute(sql);
    }

    /**
     * Процедура редактирования заявки. Добавляет комментарий и исполнителя к заявке
     * @param idDemand int уникальный номер заявки
     * @param idAdmin int уникальный номер администратора
     * @param comment String строка комментария
     */
    public void editDemand(int idDemand, int idAdmin, String comment, String status)
    {
        String sql;
        if (idAdmin != -2)
            //sql = "UPDATE demand SET d_t_id=" + idAdmin + ", d_status=\'" + status + "\', d_comment=\'" + comment + "\' WHERE d_id=" + idDemand;
            sql = "UPDATE demand SET d_t_id=" + idAdmin + ", d_status=\'В работе\', d_comment=\'" + comment + "\' WHERE d_id=" + idDemand;
        else
            sql = "UPDATE demand SET d_t_id=NULL, d_status=\'" + status + "\', d_comment=\'" + comment + "\' WHERE d_id=" + idDemand;

        this.execute(sql);
    }

    /**
     * Процедура отметки заявки о выполениия
     * @param idDemand int уникальный номер заявки
     * @param idAdmin int уникальный номер администратора, выполнившего заявку
     */
    public void executeDemand(int idDemand, int idAdmin)
    {
        String sql;
        Date d = new Date();

        if (idAdmin != -2)
            sql = "UPDATE demand SET d_t_id_execute=" + idAdmin + ", d_exec=1, d_status=\'Выполнена\', t_time_exec=" + d.getTime() + " WHERE d_id=" + idDemand;
        else
            sql = "UPDATE demand SET d_t_id=NULL, d_exec=1, d_status=\'Выполнена\', t_time_exec=" + d.getTime() + " WHERE d_id=" + idDemand;

        this.execute(sql);
    }

    public int getStaticstic()
    {
        try {
            //Получаем количество всех заявок
            //sql = "SELECT * FROM demand";
            //rs = this.query(sql);
            //int count = countRecord(rs);
            //Получаем id максимальной заявки
            String sql = "SELECT MAX(d_id) FROM demand";
            ResultSet rs = this.query(sql);
            if (countRecord(rs) > 0)
            {
                rs.next();
                int maxId = rs.getInt(1);
                return maxId;
            }
            else
                return 0;
        } catch (SQLException ex) {
            Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }

    /**
     * Функция занимается проверкой, существует ли
     * указанный администратор (а точнее его логин) в БД.
     * Полезно при создании нового администратора
     * @param login String
     * @return boolean (true - есть)
     */
    public boolean isThere(String login)
    {
        String sql = "SELECT a_id FROM admins WHERE a_login=\'" + login + "\'";
        ResultSet rs = this.query(sql);
        try {
            return countRecord(rs) > 0;
        } catch (SQLException ex) {
            return false;
        }
    }

    public Vector getComputers()
    {
        Vector vect = new Vector();
        vect.add("Все компьютеры");

        //Формируем запрос на получение списка всех компьютеров,
        //с которых была послана заявка
        String sql = "SELECT DISTINCT d_pc FROM demand";

        ResultSet rs = this.query(sql);
        
        int j = 0;
        try {
            j = countRecord(rs);
        } catch (SQLException ex) {}

        try
        {
            for (int i = 0; i < j; i++)
            {
                rs.next();
                vect.add(rs.getString(1));
            }
        }
        catch (Exception ex) {}

        return vect;
    }

    /**
     * Функция возвращает значение системного свойства
     * @param property - String - имя свойства
     * @return String
     */
    public String getSystemPropertyValue(String property)
    {
        String ret = "";
        String sql = "SELECT s_value FROM system WHERE s_name='" + property + "'";

        ResultSet rs = this.query(sql);
        try
        {
            if (countRecord(rs) > 0)
            {
                rs.next();
                ret = rs.getString(1);
            }
        }
        catch (SQLException ex) {}

        return ret;
    }

    /**
     * Функция установления значения свойства, которое уже прописано в БД
     * @param property String - имя свойства
     * @param value String - значение свойства
     */
    public void changeSystemPropertyValue(String property, String value)
    {
        String sql = "UPDATE system SET s_value='" + value + "' WHERE s_name='" + property + "'";
        this.execute(sql);
    }

    /**
     * Функция для ручного удаления всех заявок выше определенного срока
     */
    public void autoDeleteDemands()
    {
        //Получаем свойство автоудаления и преобразуем его в число
        String autodelete = this.getSystemPropertyValue("autodelete");
        int days = Integer.parseInt(autodelete);

        //Теперь получаем текущее время и высчитываем время, ниже которого удалим все заявки
        Date d  = new Date();
        long now = d.getTime();

        //Проходимся циклом, т.к. время может быть и больше
        long mil = Constants.DAY_SECONDS;
        for (int i = 0; i < days; i++)
            now -= mil;

        //Формируем запрос sql и удаляем все заявки
        String sql = "DELETE FROM demand WHERE d_time<=" + now;
        this.execute(sql);
    }

    /**
     * Функция для удаления заявок ниже указанного времени
     * @param time long - число милисекунд
     */
    public void autoDeleteDemands(long time)
    {
        String sql = "DELETE FROM demand WHERE d_time<=" + time;
        this.execute(sql);
    }

    /**
     * Функция получения количества аргументов
     * @param rs объект RecordSet, сформированный после запроса
     * @return true, если количество строк более 0
     * @throws SQLException
     */
    protected int countRecord(ResultSet rs) throws SQLException
    {
        rs.last();
        int i = rs.getRow();
        rs.beforeFirst();
        return i;
    }

}
